Mortgage Affordability Analysis

#Convert this notebook to html using pretty-jupyter
#This cell won't show up due to cell-level metadata
# Uncomment the line below and run this cell to produce the HTML report
!jupyter nbconvert --to html --template pj house_buying.ipynb

import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
import matplotlib.colors as mcolors
import streamlit as st
import plotly.express as px

# Loan and Property Parameters
purchase_price = 285_000
down_payment = 30_000
note_rate = 0.06          # Annual interest rate on mortgage note
term_years = 30           # Term of mortgage in years
effective_tax_rate = 0.0091  # Effective property tax rate (0.91%)
annual_premium = 3_120     # Annual insurance premium (Seeing costs of $3,069 - $3,732 annually)
pmi_annual_rate = 0.005     # Annual PMI rate (0.5% of loan amount) (Seeing rates of 0.3% - 1.5%)
hoa_monthly = 0          # Monthly HOA fees 
flood_annual_premium = 400  # Annual flood insurance premium (Seeing costs of $300 - $1,200 annually for FEMA Zone X)

# Additional Monthly Expenses
utilities_monthly = 243    # Monthly utilities estimate; $129 (electricity) + $43 (water/sewer) + $71 (gas)
internet_monthly = 50     # Monthly internet estimate - 1Gbps TDS Inc. Internet Package
maintenance_monthly = purchase_price * 0.01 / 12  # Monthly maintenance rate (1% of purchase price)

def monthly_payment_with_utilities(purchase_price, down_payment, note_rate, term_years, effective_tax_rate,
                    annual_premium, pmi_annual_rate, hoa_monthly, verbose=False):
    """Calcuates the total estimated monthly payment for a house including utilities and maintenance.

    Args:
        purchase_price (float): The total price of the house.
        down_payment (float): The down payment made on the house.
        note_rate (float): The annual interest rate on the mortgage.
        term_years (integer): The term of the mortgage in years.
        effective_tax_rate (float): The effective property tax rate.
        annual_premium (integer): The annual homeowners insurance premium.
        pmi_annual_rate (float): The annual mortgage insurance premium rate.
        hoa_monthly (integer): The monthly homeowners association fee.
        verbose (bool, optional): If True, prints detailed payment breakdown. Defaults to False.

    Returns:
        float: The total estimated monthly payment.
    """    
    # <1> Principal & Interest (P&I)
    L_base = purchase_price - down_payment
    i = note_rate / 12         # Monthly interest rate
    n = term_years * 12                # Total number of payments (30 years)
    P_and_I = L_base * (i * (1 + i)**n) / ((1 + i)**n - 1)  # Monthly P&I payment (Amortizing loan formula)

    # <2> Property Taxes
    taxes_monthly = (purchase_price * effective_tax_rate) / 12  # Monthly property taxes; assumes purchase price equals assessed value

    # <3> Homeowners Insurance
    hoi_monthly = annual_premium / 12   # Estimated monthly homeowners insurance

    # <4> Mortgage Insurance (PMI) - Will be included if down payment < 20%
    if down_payment < 0.20 * purchase_price:
        pmi_monthly = (L_base * pmi_annual_rate) / 12
    else:
        pmi_monthly = 0

    # <5> Flood Insurance
    flood_monthly = flood_annual_premium / 12  # Estimated monthly flood insurance

    # Total
    total_monthly_payment = P_and_I + taxes_monthly + hoi_monthly + pmi_monthly + hoa_monthly + flood_monthly + utilities_monthly + internet_monthly + maintenance_monthly

    if verbose:
        print(f"Total Estimated Monthly Payment (including utilities & maintenance): ${total_monthly_payment:,.2f}")
        print("=============================================")
        print(f"Principal & Interest (P&I): ${P_and_I:,.2f}")
        print(f"Property Taxes: ${taxes_monthly:,.2f}")
        print(f"Homeowners Insurance: ${hoi_monthly:,.2f}")
        print(f"Mortgage Insurance (PMI): ${pmi_monthly:,.2f}")
        print(f"Flood Insurance: ${flood_monthly:,.2f}")
        print(f"Utilities & Maintenance: ${utilities_monthly + internet_monthly + maintenance_monthly:,.2f}")

    return round(total_monthly_payment, 2)


def monthly_payment(purchase_price, down_payment, note_rate, term_years, effective_tax_rate,
                    annual_premium, pmi_annual_rate, hoa_monthly, verbose=False):
    """Calcuates the total estimated monthly payment for a house; does not include utilities and maintenance.

    Args:
        purchase_price (float): The total price of the house.
        down_payment (float): The down payment made on the house.
        note_rate (float): The annual interest rate on the mortgage.
        term_years (integer): The term of the mortgage in years.
        effective_tax_rate (float): The effective property tax rate.
        annual_premium (integer): The annual homeowners insurance premium.
        pmi_annual_rate (float): The annual mortgage insurance premium rate.
        hoa_monthly (integer): The monthly homeowners association fee.
        verbose (bool, optional): If True, prints detailed payment breakdown. Defaults to False.

    Returns:
        float: The total estimated monthly payment.
    """    
    
    # <1> Principal & Interest (P&I)
    L_base = purchase_price - down_payment
    i = note_rate / 12         # Monthly interest rate
    n = term_years * 12                # Total number of payments (30 years)
    P_and_I = L_base * (i * (1 + i)**n) / ((1 + i)**n - 1)  # Monthly P&I payment (Amortizing loan formula)

    # <2> Property Taxes
    taxes_monthly = (purchase_price * effective_tax_rate) / 12  # Monthly property taxes; assumes purchase price equals assessed value

    # <3> Homeowners Insurance
    hoi_monthly = annual_premium / 12   # Estimated monthly homeowners insurance

    # <4> Mortgage Insurance (PMI) - Will be included if down payment < 20%
    if down_payment < 0.20 * purchase_price:
        pmi_monthly = (L_base * pmi_annual_rate) / 12
    else:
        pmi_monthly = 0

    # <5> Flood Insurance
    flood_monthly = flood_annual_premium / 12  # Estimated monthly flood insurance

    # Total
    total_monthly_payment = P_and_I + taxes_monthly + hoi_monthly + pmi_monthly + hoa_monthly + flood_monthly

    if verbose:
        print(f"Total Estimated Monthly Payment (w/out utilities & maintenance): ${total_monthly_payment:,.2f}")
        print("=============================================")
        print(f"Principal & Interest (P&I): ${P_and_I:,.2f}")
        print(f"Property Taxes: ${taxes_monthly:,.2f}")
        print(f"Homeowners Insurance: ${hoi_monthly:,.2f}")
        print(f"Mortgage Insurance (PMI): ${pmi_monthly:,.2f}")
        print(f"Flood Insurance: ${flood_monthly:,.2f}")

    return round(total_monthly_payment, 2)

monthly_payment_with_utilities(purchase_price=purchase_price,
                down_payment=down_payment, note_rate=note_rate, term_years=term_years,
                effective_tax_rate=effective_tax_rate, annual_premium=annual_premium,
                pmi_annual_rate=pmi_annual_rate, hoa_monthly=hoa_monthly, verbose=True)

monthly_payment(purchase_price=purchase_price,
                down_payment=down_payment, note_rate=note_rate, term_years=term_years,
                effective_tax_rate=effective_tax_rate, annual_premium=annual_premium,
                pmi_annual_rate=pmi_annual_rate, hoa_monthly=hoa_monthly, verbose=True)

prices = np.round(np.arange(230_000, 300_001, 7_000), 3)    # 250k to 310k (inclusive) in 25k increments
rates = np.round(np.arange(0.050, 0.0751, 0.005), 3)        # 5.5% to 8.0% (inclusive) in 0.5% increments   

df = pd.DataFrame(index=rates, columns=prices, dtype=float)
df.index.name = 'interest_rate'
df.columns.name = 'purchase_price'

for r in rates:
    for p in prices:
        payment = monthly_payment(purchase_price=p, down_payment=down_payment, note_rate=r, term_years=term_years,
                        effective_tax_rate=effective_tax_rate, annual_premium=annual_premium, pmi_annual_rate=pmi_annual_rate, 
                        hoa_monthly=hoa_monthly, verbose=False)
        df.loc[r, p] = round(payment, 2)    

df_display = df.copy()
df_display.index = (df_display.index * 100).map('{:.2f}%'.format)
df_display.columns = df_display.columns.map('${:,.0f}'.format)
df_display = df_display.style.format('${:,.2f}')
df_display

labels = df.applymap(lambda v: f"${v:,.2f}")

plt.figure(figsize=(12,10))
ax = sns.heatmap(df,               
                 cmap='RdYlGn_r',     
                 linewidths=0.5,
                 cbar_kws={'label':'Monthly Payment'},
                 annot=labels, 
                 fmt="",
                 vmin=1_700, vmax=2_025)

ax.set_xlabel('Purchase Price')
ax.set_ylabel('Interest Rate')

ylabels = ['{:.1f}'.format(float(x._text)*100)  + "%" for x in ax.get_yticklabels()]
ax.set_yticklabels(ylabels)

xlabels = ['${:,.2f}'.format(float(x._text)) for x in ax.get_xticklabels()]
ax.set_xticklabels(xlabels)

ax.set_xticklabels([f"${c:,.0f}" for c in df.columns], rotation=45, ha="right")
ax.set_yticklabels([f"{r:.1%}" for r in df.index], rotation=0)

cbar = ax.collections[0].colorbar
cbar.formatter = mtick.StrMethodFormatter("${x:,.0f}")
cbar.update_ticks()

plt.title('Monthly Payment\nby Purchase Price & Interest Rate')
plt.tight_layout()
plt.show()